/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



package helpdesk;

import java.util.ArrayList;

import java.util.Date;

import java.sql.Timestamp;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Represents an update to a help ticket.
 *
 * @author brian
 */
public class HelpTicketUpdate {
    int updateId;
    int ticketId;
    int userId;
    String userName;
    String contents;
    Date date;
    boolean visible;

    /**
     * Should we be able to see this update? This was in case we needed to
     * delete some updates without actually deleting them. May not be
     * used.
     *
     * @return
     */
    public boolean isVisible() {
        return visible;
    }

    /**
     * Set whether or not we can see this ticket. Does not update the DB.
     * @param visible
     */
    public void setVisible(boolean visible) {
        this.visible = visible;
    }

    /**
     * Get the contents of this update.
     * @return
     */
    public String getContents() {
        return contents;
    }

    /**
     * Set the contents of this update. Does not update the DB.
     * @param contents
     */
    public void setContents(String contents) {
        this.contents = contents;
    }

    /**
     * Get when this update was created.
     *
     * @return
     */
    public Date getDate() {
        return date;
    }

    /**
     * Get the id of the ticket that this update belongs to
     * @return
     */
    public int getTicketId() {
        return ticketId;
    }

    /**
     * Set the id of the ticket that this update belongs to. Does not update the
     * DB.
     * @param ticketId
     */
    public void setTicketId(int ticketId) {
        this.ticketId = ticketId;
    }

    /**
     * Get the id representing this update.
     * @return
     */
    public int getUpdateId() {
        return updateId;
    }

    /**
     * Get the id of the user that created this update.
     * @return
     */
    public int getUserId() {
        return userId;
    }

    /**
     * Set the id of the user that created this update. Does not update the DB.
     * @param userId
     */
    public void setUserId(int userId) {
        this.userId = userId;
    }

    /**
     * Get the email of the user that created this update.
     * @return
     */
    public String getUserName() {
        return userName;
    }

    /**
     * Get all updates for the help ticket specified by ticketId.
     *
     * @param ticketId
     * @return
     * @throws SQLException
     */
    public static ArrayList<HelpTicketUpdate> getByTicketId(int ticketId) throws SQLException
    {
        ArrayList<HelpTicketUpdate> helpTicketUpdates = null;
        Connection conn = null;
        PreparedStatement ps;
        ResultSet rs;

        try
        {
            helpTicketUpdates = new ArrayList<HelpTicketUpdate>();
            conn = DBManager.getConnection();
            ps = conn.prepareStatement("SELECT help_ticket_update.updateid, help_ticket_update.ticketid, " +
                    "help_ticket_update.userid, help_ticket_update.contents, help_ticket_update.datetime, " +
                    "help_ticket_update.visible, users.email FROM help_ticket_update, users WHERE ticketid=? " +
                    "AND help_ticket_update.userid=users.userid ORDER BY help_ticket_update.updateid");
            ps.setInt(1, ticketId);
            rs = ps.executeQuery();
            while (rs.next())
            {
                HelpTicketUpdate htu = new HelpTicketUpdate();
                htu.updateId = rs.getInt(1);
                htu.ticketId = rs.getInt(2);
                htu.userId = rs.getInt(3);
                htu.contents = rs.getString(4);
                htu.date = rs.getTimestamp(5);
                htu.visible = rs.getBoolean(6);
                htu.userName = rs.getString(7);
                helpTicketUpdates.add(htu);
            }
        }
        finally
        {
            if (conn != null)
                conn.close();
        }

        return helpTicketUpdates;
    }

    /**
     * Create a new ticket in the database. Sets the creation date to the
     * current time. Only requires a userid, ticketid, and contents. A trigger
     * in the database causes the modified field of the ticket to be set to the
     * creation date of this update.
     *
     * @throws SQLException
     */
    public void create() throws SQLException
    {
        Connection conn = null;
        PreparedStatement ps;
        Timestamp currentDate = new Timestamp((new Date()).getTime());



        try
        {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement("INSERT INTO help_ticket_update (ticketid, userid, contents, datetime) VALUES (?, ?, ?, ?)");
            ps.setInt(1, ticketId);
            ps.setInt(2, userId);
            ps.setString(3, contents);
            ps.setTimestamp(4, currentDate);
            ps.executeUpdate();
            conn.commit();
        }
        finally
        {
            if (conn != null)
                conn.close();
        }
    }
}
